import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import os
import sys
from dbfread import DBF
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
import threading
import struct
from datetime import datetime

class DBFExcelConverter:
    def __init__(self, root):
        self.root = root
        self.root.title("DBF ⇄ Excel 双向转换工具")
        self.root.geometry("700x500")
        self.root.resizable(True, True)
        
        # 设置窗口图标（如果有的话）
        try:
            self.root.iconbitmap("icon.ico")
        except:
            pass
        
        # 文件路径变量
        self.input_file = tk.StringVar()
        self.output_file = tk.StringVar()
        
        # 转换模式
        self.conversion_mode = tk.StringVar(value="dbf_to_excel")
        
        # 创建界面
        self.create_widgets()
        
        # 转换状态
        self.converting = False
        
    def create_widgets(self):
        # 主框架
        main_frame = ttk.Frame(self.root, padding="10")
        main_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        
        # 配置网格权重
        self.root.columnconfigure(0, weight=1)
        self.root.rowconfigure(0, weight=1)
        main_frame.columnconfigure(1, weight=1)
        
        # 标题
        title_label = ttk.Label(main_frame, text="DBF ⇄ Excel 双向转换工具", 
                               font=("Arial", 16, "bold"))
        title_label.grid(row=0, column=0, columnspan=3, pady=(0, 20))
        
        # 转换模式选择
        mode_frame = ttk.LabelFrame(main_frame, text="转换模式", padding="10")
        mode_frame.grid(row=1, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=(0, 15))
        
        ttk.Radiobutton(mode_frame, text="DBF → Excel", variable=self.conversion_mode, 
                       value="dbf_to_excel", command=self.update_file_labels).grid(row=0, column=0, sticky=tk.W, padx=(0, 20))
        ttk.Radiobutton(mode_frame, text="Excel → DBF", variable=self.conversion_mode, 
                       value="excel_to_dbf", command=self.update_file_labels).grid(row=0, column=1, sticky=tk.W)
        
        # 输入文件选择
        self.input_label = ttk.Label(main_frame, text="选择DBF文件:")
        self.input_label.grid(row=2, column=0, sticky=tk.W, pady=5)
        input_entry = ttk.Entry(main_frame, textvariable=self.input_file, width=50)
        input_entry.grid(row=2, column=1, sticky=(tk.W, tk.E), padx=(5, 5), pady=5)
        ttk.Button(main_frame, text="浏览", command=self.browse_input_file).grid(row=2, column=2, pady=5)
        
        # 输出文件选择
        self.output_label = ttk.Label(main_frame, text="保存Excel文件:")
        self.output_label.grid(row=3, column=0, sticky=tk.W, pady=5)
        output_entry = ttk.Entry(main_frame, textvariable=self.output_file, width=50)
        output_entry.grid(row=3, column=1, sticky=(tk.W, tk.E), padx=(5, 5), pady=5)
        ttk.Button(main_frame, text="浏览", command=self.browse_output_file).grid(row=3, column=2, pady=5)
        
        # 转换选项框架
        options_frame = ttk.LabelFrame(main_frame, text="转换选项", padding="10")
        options_frame.grid(row=4, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=20)
        options_frame.columnconfigure(1, weight=1)
        
        # 包含表头选项
        self.include_header = tk.BooleanVar(value=True)
        ttk.Checkbutton(options_frame, text="包含表头", variable=self.include_header).grid(row=0, column=0, sticky=tk.W)
        
        # 格式化选项
        self.format_output = tk.BooleanVar(value=True)
        ttk.Checkbutton(options_frame, text="格式化输出", variable=self.format_output).grid(row=0, column=1, sticky=tk.W)
        
        # 编码选项（仅Excel转DBF时显示）
        self.encoding_label = ttk.Label(options_frame, text="DBF编码:")
        self.encoding_var = tk.StringVar(value="gbk")
        self.encoding_combo = ttk.Combobox(options_frame, textvariable=self.encoding_var, 
                                          values=["gbk", "utf-8", "gb2312"], width=10, state="readonly")
        
        # 进度条
        self.progress = ttk.Progressbar(main_frame, mode='indeterminate')
        self.progress.grid(row=5, column=0, columnspan=3, sticky=(tk.W, tk.E), pady=10)
        
        # 状态标签
        self.status_label = ttk.Label(main_frame, text="准备就绪", foreground="green")
        self.status_label.grid(row=6, column=0, columnspan=3, pady=5)
        
        # 按钮框架
        button_frame = ttk.Frame(main_frame)
        button_frame.grid(row=7, column=0, columnspan=3, pady=20)
        
        # 转换按钮
        self.convert_button = ttk.Button(button_frame, text="开始转换", 
                                        command=self.start_conversion, style="Accent.TButton")
        self.convert_button.pack(side=tk.LEFT, padx=5)
        
        # 退出按钮
        ttk.Button(button_frame, text="退出", command=self.root.quit).pack(side=tk.LEFT, padx=5)
        
        # 日志文本框
        log_frame = ttk.LabelFrame(main_frame, text="转换日志", padding="5")
        log_frame.grid(row=8, column=0, columnspan=3, sticky=(tk.W, tk.E, tk.N, tk.S), pady=10)
        log_frame.columnconfigure(0, weight=1)
        log_frame.rowconfigure(0, weight=1)
        main_frame.rowconfigure(8, weight=1)
        
        # 创建文本框和滚动条
        text_frame = ttk.Frame(log_frame)
        text_frame.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        text_frame.columnconfigure(0, weight=1)
        text_frame.rowconfigure(0, weight=1)
        
        self.log_text = tk.Text(text_frame, height=8, wrap=tk.WORD)
        scrollbar = ttk.Scrollbar(text_frame, orient=tk.VERTICAL, command=self.log_text.yview)
        self.log_text.configure(yscrollcommand=scrollbar.set)
        
        self.log_text.grid(row=0, column=0, sticky=(tk.W, tk.E, tk.N, tk.S))
        scrollbar.grid(row=0, column=1, sticky=(tk.N, tk.S))
        
    def update_file_labels(self):
        """根据转换模式更新文件标签"""
        if self.conversion_mode.get() == "dbf_to_excel":
            self.input_label.config(text="选择DBF文件:")
            self.output_label.config(text="保存Excel文件:")
            # 隐藏编码选项
            self.encoding_label.grid_remove()
            self.encoding_combo.grid_remove()
        else:
            self.input_label.config(text="选择Excel文件:")
            self.output_label.config(text="保存DBF文件:")
            # 显示编码选项
            self.encoding_label.grid(row=1, column=0, sticky=tk.W, pady=(5, 0))
            self.encoding_combo.grid(row=1, column=1, sticky=tk.W, pady=(5, 0))
        
        # 清空文件路径
        self.input_file.set("")
        self.output_file.set("")
        
    def browse_input_file(self):
        if self.conversion_mode.get() == "dbf_to_excel":
            filename = filedialog.askopenfilename(
                title="选择DBF文件",
                filetypes=[("DBF文件", "*.dbf"), ("所有文件", "*.*")]
            )
            if filename:
                self.input_file.set(filename)
                base_name = os.path.splitext(filename)[0]
                self.output_file.set(f"{base_name}.xlsx")
        else:
            filename = filedialog.askopenfilename(
                title="选择Excel文件",
                filetypes=[("Excel文件", "*.xlsx;*.xls"), ("XLSX文件", "*.xlsx"), ("XLS文件", "*.xls"), ("所有文件", "*.*")]
            )
            if filename:
                self.input_file.set(filename)
                base_name = os.path.splitext(filename)[0]
                self.output_file.set(f"{base_name}.dbf")
        
        if filename:
            self.log_message(f"已选择输入文件: {filename}")
    
    def browse_output_file(self):
        if self.conversion_mode.get() == "dbf_to_excel":
            filename = filedialog.asksaveasfilename(
                title="保存Excel文件",
                defaultextension=".xlsx",
                filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")]
            )
        else:
            filename = filedialog.asksaveasfilename(
                title="保存DBF文件",
                defaultextension=".dbf",
                filetypes=[("DBF文件", "*.dbf"), ("所有文件", "*.*")]
            )
        
        if filename:
            self.output_file.set(filename)
            self.log_message(f"已设置输出文件: {filename}")
    
    def log_message(self, message):
        self.log_text.insert(tk.END, f"{message}\n")
        self.log_text.see(tk.END)
        self.root.update_idletasks()
    
    def start_conversion(self):
        if self.converting:
            return
            
        input_path = self.input_file.get().strip()
        output_path = self.output_file.get().strip()
        
        if not input_path:
            messagebox.showerror("错误", "请选择输入文件")
            return
            
        if not output_path:
            messagebox.showerror("错误", "请选择输出文件路径")
            return
            
        if not os.path.exists(input_path):
            messagebox.showerror("错误", "输入文件不存在")
            return
        
        # 在新线程中执行转换
        self.converting = True
        self.convert_button.config(state="disabled")
        self.progress.start()
        self.status_label.config(text="正在转换...", foreground="blue")
        
        if self.conversion_mode.get() == "dbf_to_excel":
            thread = threading.Thread(target=self.convert_dbf_to_excel, args=(input_path, output_path))
        else:
            thread = threading.Thread(target=self.convert_excel_to_dbf, args=(input_path, output_path))
        
        thread.daemon = True
        thread.start()
    
    def convert_dbf_to_excel(self, input_path, output_path):
        """DBF转Excel"""
        try:
            self.log_message("开始转换DBF文件...")
            
            # 读取DBF文件
            self.log_message("正在读取DBF文件...")
            db = DBF(input_path, encoding='gbk')
            
            # 获取字段信息
            fields = db.field_names
            self.log_message(f"发现 {len(fields)} 个字段")
            self.log_message(f"字段列表: {', '.join(fields)}")
            
            # 读取数据
            data = []
            record_count = 0
            for record in db:
                row_data = [str(record.get(field, '')).strip() for field in fields]
                data.append(row_data)
                record_count += 1
                if record_count % 1000 == 0:
                    self.log_message(f"已处理 {record_count} 条记录...")
            self.log_message(f"共读取 {record_count} 条记录")
            
            # 创建DataFrame
            df = pd.DataFrame(data, columns=fields if self.include_header.get() else None)
            
            # 保存为Excel
            self.log_message("正在保存为Excel文件...")
            
            with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
                df.to_excel(writer, sheet_name='Sheet1', index=False, header=self.include_header.get())
                
                # 如果启用格式化
                if self.format_output.get():
                    self.log_message("正在应用格式化...")
                    workbook = writer.book
                    worksheet = writer.sheets['Sheet1']
                    
                    # 设置表头样式
                    if self.include_header.get():
                        header_font = Font(bold=True)
                        header_fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
                        
                        for col in range(1, len(fields) + 1):
                            cell = worksheet.cell(row=1, column=col)
                            cell.font = header_font
                            cell.fill = header_fill
                    
                    # 调整列宽
                    for col in range(1, len(fields) + 1):
                        column_letter = worksheet.cell(row=1, column=col).column_letter
                        max_length = 0
                        for row in range(1, record_count + 2):
                            cell_value = worksheet.cell(row=row, column=col).value
                            if cell_value:
                                max_length = max(max_length, len(str(cell_value)))
                        adjusted_width = min(max_length + 2, 50)
                        worksheet.column_dimensions[column_letter].width = adjusted_width
            
            self.log_message("转换完成！")
            self.log_message(f"文件已保存到: {output_path}")
            
            # 更新UI
            self.root.after(0, self.conversion_completed, True)
            
        except Exception as e:
            error_msg = f"转换过程中出现错误: {str(e)}"
            self.log_message(error_msg)
            self.root.after(0, self.conversion_completed, False, error_msg)
    
    def convert_excel_to_dbf(self, input_path, output_path):
        """Excel转DBF"""
        try:
            self.log_message("开始转换Excel文件...")
            
            # 读取Excel文件
            self.log_message("正在读取Excel文件...")
            df = pd.read_excel(input_path, header=0 if self.include_header.get() else None)
            
            self.log_message(f"发现 {len(df.columns)} 个字段")
            self.log_message(f"共读取 {len(df)} 条记录")
            
            # 获取字段信息
            if self.include_header.get():
                fields = list(df.columns)
            else:
                fields = [f"FIELD_{i+1}" for i in range(len(df.columns))]
            
            self.log_message(f"字段列表: {', '.join(fields)}")
            
            # 创建DBF文件
            self.log_message("正在创建DBF文件...")
            self.create_dbf_file(output_path, fields, df, self.encoding_var.get())
            
            self.log_message("转换完成！")
            self.log_message(f"文件已保存到: {output_path}")
            
            # 更新UI
            self.root.after(0, self.conversion_completed, True)
            
        except Exception as e:
            error_msg = f"转换过程中出现错误: {str(e)}"
            self.log_message(error_msg)
            self.root.after(0, self.conversion_completed, False, error_msg)
    
    def create_dbf_file(self, filename, field_names, dataframe, encoding='gbk'):
        """创建DBF文件的简单实现"""
        try:
            # 分析数据类型并创建字段定义
            field_specs = []
            for col in field_names:
                if col in dataframe.columns:
                    sample_data = dataframe[col].dropna()
                    if len(sample_data) > 0:
                        sample_value = sample_data.iloc[0]
                        
                        # 判断数据类型
                        if pd.api.types.is_numeric_dtype(sample_data):
                            if pd.api.types.is_integer_dtype(sample_data):
                                max_val = sample_data.abs().max()
                                if max_val < 128:
                                    field_specs.append((col[:10], 'N', 3, 0))
                                elif max_val < 32768:
                                    field_specs.append((col[:10], 'N', 6, 0))
                                else:
                                    field_specs.append((col[:10], 'N', 12, 0))
                            else:
                                field_specs.append((col[:10], 'N', 12, 2))
                        elif pd.api.types.is_datetime64_any_dtype(sample_data):
                            field_specs.append((col[:10], 'D', 8, 0))
                        else:
                            # 字符型，计算最大长度
                            max_len = sample_data.astype(str).str.len().max()
                            max_len = min(max(max_len, 10), 254)
                            field_specs.append((col[:10], 'C', max_len, 0))
                    else:
                        field_specs.append((col[:10], 'C', 50, 0))
                else:
                    field_specs.append((col[:10], 'C', 50, 0))
            
            # 创建DBF文件头
            now = datetime.now()
            header = bytearray(32)
            header[0] = 0x03  # DBF版本
            header[1] = now.year - 1900
            header[2] = now.month
            header[3] = now.day
            
            # 记录数量
            record_count = len(dataframe)
            header[4:8] = struct.pack('<L', record_count)
            
            # 头部长度
            header_len = 32 + len(field_specs) * 32 + 1
            header[8:10] = struct.pack('<H', header_len)
            
            # 记录长度
            record_len = 1  # 删除标记
            for spec in field_specs:
                record_len += spec[2]
            header[10:12] = struct.pack('<H', record_len)
            
            # 写入DBF文件
            with open(filename, 'wb') as f:
                # 写入文件头
                f.write(header)
                
                # 写入字段描述
                for field_name, field_type, field_len, field_dec in field_specs:
                    field_desc = bytearray(32)
                    name_bytes = field_name.encode(encoding, errors='ignore')[:11]
                    field_desc[:len(name_bytes)] = name_bytes
                    field_desc[11] = ord(field_type)
                    field_desc[16] = field_len
                    field_desc[17] = field_dec
                    f.write(field_desc)
                
                # 写入字段描述结束符
                f.write(b'\r')
                
                # 写入数据记录
                for idx, row in dataframe.iterrows():
                    # 删除标记
                    f.write(b' ')
                    
                    # 写入字段数据
                    for i, (field_name, field_type, field_len, field_dec) in enumerate(field_specs):
                        if field_name in dataframe.columns:
                            value = row[field_name]
                        else:
                            value = ''
                        
                        if pd.isna(value):
                            value = ''
                        
                        if field_type == 'C':
                            # 字符型
                            value_str = str(value)[:field_len]
                            value_bytes = value_str.encode(encoding, errors='ignore')
                            value_bytes = value_bytes[:field_len]
                            value_bytes += b' ' * (field_len - len(value_bytes))
                        elif field_type == 'N':
                            # 数值型
                            if field_dec > 0:
                                value_str = f"{float(value):.{field_dec}f}"
                            else:
                                value_str = str(int(float(value)) if pd.notna(value) and value != '' else 0)
                            value_str = value_str.rjust(field_len)[:field_len]
                            value_bytes = value_str.encode('ascii', errors='ignore')
                        elif field_type == 'D':
                            # 日期型
                            if pd.notna(value):
                                if isinstance(value, str):
                                    try:
                                        date_obj = pd.to_datetime(value)
                                        value_str = date_obj.strftime('%Y%m%d')
                                    except:
                                        value_str = '        '
                                else:
                                    value_str = pd.to_datetime(value).strftime('%Y%m%d')
                            else:
                                value_str = '        '
                            value_bytes = value_str.encode('ascii')
                        
                        f.write(value_bytes)
                
                # 写入文件结束符
                f.write(b'\x1A')
                
        except Exception as e:
            raise Exception(f"创建DBF文件失败: {str(e)}")
    
    def conversion_completed(self, success, error_msg=None):
        self.converting = False
        self.convert_button.config(state="normal")
        self.progress.stop()
        
        if success:
            self.status_label.config(text="转换成功", foreground="green")
            messagebox.showinfo("成功", "文件转换成功！")
        else:
            self.status_label.config(text="转换失败", foreground="red")
            messagebox.showerror("错误", error_msg or "转换失败")

def main():
    root = tk.Tk()
    app = DBFExcelConverter(root)
    
    # 设置窗口居中
    root.update_idletasks()
    width = root.winfo_width()
    height = root.winfo_height()
    x = (root.winfo_screenwidth() // 2) - (width // 2)
    y = (root.winfo_screenheight() // 2) - (height // 2)
    root.geometry(f"{width}x{height}+{x}+{y}")
    
    root.mainloop()

if __name__ == "__main__":
    main() 